if ( object_id('example_of_comdiv_tsql_extensions_as_usual') is not null ) 	drop proc example_of_comdiv_tsql_extensions_as_usual
go
create proc example_of_comdiv_tsql_extensions_as_usual as begin	print ' procedure ''example_of_comdiv_tsql_extensions''	started '
	begin try
		begin tran	
			declare @x int,@y nvarchar(10)
			begin try
				declare __x cursor for
					select object_id,  type from sys.objects
				open __x
				fetch next from __x into @x,@y 
				while @@fetch_status = 0 begin
					print @x 
					print @y
					fetch next from __x into @x,@y 	
				end
			close __x
			deallocate __x
			end try
			begin catch
				close __x
				deallocate __x
				exec _throw '',18,'error in cursor'
			end catch
		commit
	end try
	begin catch
		if @@trancount > 0 rollback
		print ' example_of_comdiv_tsql_extensions ROLLBACKED !!! '
		exec _throw 'example_of_comdiv_tsql_extensions', 18, 'transactional error'
	end catch
	print 'procedure ''example_of_comdiv_tsql_extensions'' finished '
 end
GO

-- LOOK CODE BELOW IS EXACTLY THE SAME AS CODE UPPER!!!!!!!!!!!!!!
-- and it does not need any custom tools, .NET Assemblies
-- or others...
-- just native T-SQL with well formed DDL trigger and some trikki procedures that it used... 
-- look at this:





-- all non-functional stuff will be embeded in AOP style
-- just 'create proc' command needed, no dropping, name provided by attribute
-- at start you see block of some declarations and attributes
---------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------
GO
/*@
<name>example_of_comdiv_tsql_extensions_new_way</name>	-- here we provide real name of procedure
<showsqloncreate/>										-- instructs to print sql that is created
<trace/>												-- inserts trace prints on enter and leave of proc
<transaction/>											-- wrap all body of procedure into transaction
@*/
create proc _ as begin	
	declare @x int,@y nvarchar(10)
	--foreach: 
		select @x=object_id, @y = type from sys.objects
	--begin:
		print @x
		print @y
	--end;
end
go
----------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------

	-- as you see proc is still well-formed T-SQL, so it will be executed without error and		
	-- then comdiv TRIGGER do all of it work, not special editor or special environment needed
	-- just native T-SQL


--METRICS
--------------------------------------------------------------------------------------------------------------------------------
-- METRIC							| SOURCE			| NEW VERSION		|	COMMENT
--------------------------------------------------------------------------------------------------------------------------------
-- commands							| 2					| 1					| not need _GO_ parsing or using SQLWB
-- lines of code					| 33				| 15				| >50%
-- lines of non-functional code		| 29				| 2					| all native T-SQL stuff embeded by trigger
-- points of name desinchronization | 6					| 0					| don't need mind about renaming
-- copy-paste snippets*				| 8					| 1					| only attributes and --foreach: leads to copy-paste	
--------------------------------------------------------------------------------------------------------------------------------																								

-- *	points of code which seems to be copyed between different procedures that make not real templating, but code doubling
--		in new style - just XML attributes block cause such intention

-- to cleanup dbase from test procedures select and run
-- AS YOU SEE IN 'print' trace - new procedure have line number in any line, so reading of error
-- messages is more simple task - you can quickly find line in souce
GO
-- EXAMPLE OF TABLE INTERFACES


-- BASE INTERFACE
/*@
<name>comdiv_table_interface.base</name>
<field name="type1" type="int" constraint="not null"/>
<description>this is base interface</description>
<trace/>
<showsqloncreate/>
@*/
create proc _ @table nvarchar(255), @useconstraints bit as begin 
	return 
end
-- all code will be injected by preprocessor
go
-- INHERITED INTERFACE WITH SPECIAL CODE
/*@
<name>comdiv_table_interface.main</name>

<import>base</import>
<field name="type2" type="int" constraint="not null"/>
<method name="gettype2">
	create function __ (@id int) returns int as begin
		return (select top 1 type2 from {{@table}} where id = @id)
	end
</method>
<description>this is interface that imports other interface and makes own work</description>
<showsqloncreate/>
<trace/>
@*/
create proc _ as begin return end
go
--so now we can create table and make interface support to it
if object_id('test_table_1') is not null drop table test_table_1
go
exec _drop 'test_table_1_gettype2' -- assure that function is not existed else
go
exec _table 'test_table_1' --create table with just id field
go
exec _interface 'test_table_1','main' -- apply interface
go
insert test_table_1 (type1,type2) values (1,2) -- assume that both needed fields exists on table
declare @id bigint set @id = scope_identity()
select dbo.test_table_1_gettype2(@id) --assume that function was created
go


/* -- execute this script to fully cleanup database from example artifacts
exec _drop 'example_of_comdiv_tsql_extensions_new_way'
exec _drop 'example_of_comdiv_tsql_extensions_as_usual'
go
drop table test_table_1
go
exec _drop 'test_table_1_gettype2'
exec _drop 'comdiv_table_interface.main'
exec _drop 'comdiv_table_interface.base'
*/